﻿using System;
using System.Collections.Generic;
using System.Web;
using Quartz;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using HySoft.Cash.Business;
using HySoft.Cash.Structure;
using HySoft.Cash;


namespace PayRoller.Code
{
    public class InvoiceJob : IJob
    {
        #region IJob 成员

        public void Execute(JobExecutionContext context)
        {
            DataBaseLoger.SaveExcuteInfo("InvoiceTask", string.Empty);
            ExecuteTask();
        }
        #endregion


        /// <summary>
        /// 
        /// </summary>
        public static void ExecuteTask()
        {
            ExecuteInvoceTask();
            ExcuteStatementTast();
        }

        /// <summary>
        /// 执行Statement任务
        /// </summary>
        private static void ExcuteStatementTast()
        {
            GenericStatement();
        }

        private static void GenericStatement()
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SQLConnString"]))
            {
                bool isNeedGenericStatement = false;
                conn.Open();

                //1.需要判断本月的Statement是否已经生成了
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;

                    string commString = "SELECT [OptionValue]  FROM  [tSysOption] WHERE [Sid]=6 OR [OptionName]='LastStatementDate'";
                    comm.CommandText = commString;
                    DateTime lastStatementDate = Convert.ToDateTime(comm.ExecuteScalar());
                    if (lastStatementDate.Month >= SafeDateTime.LocalToday.Month)
                    {
                        isNeedGenericStatement = false;
                    }
                    else
                    {
                        if (SafeDateTime.LocalToday.Day >= AppSettings.GenericStatementDay)
                        {
                            isNeedGenericStatement = true;
                        }
                        else
                        {
                            isNeedGenericStatement = false;
                        }
                    }
                }


                
                if (isNeedGenericStatement == true)
                {
                    //2.获取所有活动的Invoice，为后面生成Statement做准备
                    Dictionary<int, List<TinvoicelistDT>> dicOrg = new Dictionary<int, List<TinvoicelistDT>>();
                    using (SqlCommand comm = new SqlCommand())
                    {
                        comm.Connection = conn;
                        comm.CommandText = string.Format("select * from tInvoiceList where IsValid={0} or IsValid={1}", (int)InvoiceStatuses.InitStatus, (int)InvoiceStatuses.Overdue);
                        using (SqlDataReader reader = comm.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                TinvoicelistDT invoiceDT = new TinvoicelistDT();
                                invoiceDT = TinvoicelistDT.ConvertFrom(reader);
                                if (dicOrg.ContainsKey(invoiceDT.OrgSid))
                                {
                                    dicOrg[invoiceDT.OrgSid].Add(invoiceDT);
                                }
                                else
                                {
                                    List<TinvoicelistDT> list = new List<TinvoicelistDT>();
                                    list.Add(invoiceDT);
                                    dicOrg.Add(invoiceDT.OrgSid, list);
                                }
                            }
                            reader.Close();
                        }
                    }

                    //3.生成具体的Statement
                    SqlTransaction tran = conn.BeginTransaction();
                    try
                    {
                        using (SqlCommand comm = new SqlCommand())
                        {
                            comm.Connection = conn;
                            comm.Transaction = tran;
                            string commString = string.Empty;
                            foreach (KeyValuePair<int, List<TinvoicelistDT>> kvp in dicOrg)
                            {
                                Guid statementGuid = Guid.NewGuid();
                                string commStringInvoice = string.Empty;
                                decimal statementPrincipal = 0M;
                                decimal statementInterest = 0M;
                                decimal statementPenalty = 0M;
                                decimal statementLateInterestCharges = 0M;
                                decimal statementPrincipalPaid = 0M;
                                decimal statementInterestPaid = 0M;
                                decimal statementPenaltyPaid = 0M;
                                decimal statementLateInterestChargesPaid = 0M;
                                foreach (TinvoicelistDT invoiceDT in kvp.Value)
                                {
                                    Guid newInvoceGuid = Guid.NewGuid();
                                    
                                    commStringInvoice += string.Format("INSERT INTO [tStatementInvoice]([StatementGuid],[Guid],[OriginalInvoiceID],[OriginalInvoiceGuid],[OrgSid],[InvoiceDate],[Principal],[Interest],[Penalty],[LateInterestCharges],[state],[Note],[PrincipalPaid],[InterestPaid],[PenaltyPaid],[LateInterestChargesPaid]) VALUES ('{0}' ,'{1}' ,{2},'{3}' ,{4} ,'{5}' ,{6} ,{7} ,{8} ,{9} ,{10} ,'{11}',{12} ,{13} ,{14} ,{15});",
                                        statementGuid, newInvoceGuid, invoiceDT.Sid, invoiceDT.Guid, invoiceDT.OrgSid, invoiceDT.InvoiceDate, invoiceDT.Principal, invoiceDT.Interest, invoiceDT.Penalty, invoiceDT.LateInterestCharges, (int)invoiceDT.IsValid, string.Empty, invoiceDT.PrincipalPaid, invoiceDT.InterestPaid, invoiceDT.PenaltyPaid, invoiceDT.LateInterestChargesPaid);
                                    statementPrincipal += invoiceDT.Principal;
                                    statementPrincipalPaid += invoiceDT.PrincipalPaid;
                                    statementPenalty += invoiceDT.Penalty;
                                    statementPenaltyPaid += invoiceDT.PenaltyPaid;
                                    statementInterest += invoiceDT.Interest;
                                    statementInterestPaid += invoiceDT.InterestPaid;
                                    statementLateInterestCharges += invoiceDT.LateInterestCharges;
                                    statementLateInterestChargesPaid += invoiceDT.LateInterestChargesPaid;
                                }

                                string sqlTempClosingBalance = string.Format("([dbo].[UF_GetCreditBalance]({0}))", kvp.Key);
                                string sqlTempCurrentBalance = string.Format("([dbo].[UF_GetFeeBalance]({0}))", kvp.Key);
                                string commStringStatement = string.Format("INSERT INTO [tStatementMaster] ([Guid] ,[OrgID] ,[StatementDate] ,[Memo] ,[TotalPrincipal] ,[TotalInterest] ,[TotalPenalty] ,[TotalLateInterestCharges] ,[TotalPrincipalPaid] ,[TotalInterestPaid] ,[TotalPenaltyPaid] ,[TotalLateInterestChargesPaid] ,[Status] ,[SendStatus],[LastCreditBalance],[LastFeeBalance]) VALUES ('{0}' ,{1} ,'{2}','{3}',{4} ,{5},{6} ,{7} ,{8} ,{9} ,{10} ,{11} ,{12} ,{13},{14},{15});",
                                    statementGuid, kvp.Key, SafeDateTime.LocalToday, string.Empty, statementPrincipal, statementInterest, statementPenalty, statementLateInterestCharges, statementPrincipalPaid, statementInterestPaid, statementPenaltyPaid, statementLateInterestChargesPaid, (int)StatementStatuses.Normal, (int)StatementSendStatuses.NeedSend, sqlTempClosingBalance, sqlTempCurrentBalance);

                                commString = commStringStatement + commStringInvoice;
                                comm.CommandText = commString;
                                comm.ExecuteNonQuery();
                            }

                            //更新Statement最后的生成日期
                            comm.CommandText =string.Format( "UPDATE [tSysOption] SET [OptionValue] = '{0}' WHERE [Sid] = 6 OR [OptionName]='LastStatementDate'",SafeDateTime.LocalToday);
                            comm.ExecuteNonQuery();
                        }


                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                    }
                }
            }
        }

        /// <summary>
        /// 执行Invoice任务
        /// </summary>
        /// <param name="deadLine"></param>
        private static void ExecuteInvoceTask()
        {
            //最后的时间（此时间前所有未生成账单的payment都将参加计算）
            DateTime deadLine = SafeDateTime.LocalToday;
            GenericInvoice(deadLine);
            ChangeInvoiceStatus();
        }

        /// <summary>
        /// 改变Invoice账单的状态
        /// </summary>
        private static void ChangeInvoiceStatus()
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SQLConnString"]))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();

                try
                {
                    int overDueDays = AppSettings.InvoiceOverDueDays;
                    string commString = string.Format("update tInvoiceList set IsValid= {0} where InvoiceDate< '{1}' and [IsValid]={2} ", (int)InvoiceStatuses.Overdue, SafeDateTime.LocalToday.AddDays(-overDueDays), (int)InvoiceStatuses.InitStatus);
                    using (SqlCommand comm = new SqlCommand())
                    {
                        comm.Transaction = tran;
                        comm.Connection = conn;
                        comm.CommandText = commString;
                        comm.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                }
            }
        }

        /// <summary>
        /// 生成Invoice账单
        /// </summary>
        /// <param name="deadLine"></param>
        private static void GenericInvoice(DateTime deadLine)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SQLConnString"]))
            {
                conn.Open();

                Dictionary<DateTime, Dictionary<int, List<TemppaymentDT>>> dicDate = new Dictionary<DateTime, Dictionary<int, List<TemppaymentDT>>>();
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;
                    comm.CommandText = "select * from tEmpPayment where InvoiceStatus=0 and PaymentDate< '" + deadLine + "'";
                    using (SqlDataReader reader = comm.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            TemppaymentDT payDT = new TemppaymentDT();
                            payDT.Sid = Convert.ToInt32(reader["Sid"]);
                            payDT.OrgSid = Convert.ToInt32(reader["OrgSid"]);
                            payDT.PaymentAmount = Convert.ToDecimal(reader["PaymentAmount"]);
                            payDT.PaymentDate = Convert.ToDateTime(reader["PaymentDate"]);
                            DateTime payDate = payDT.PaymentDate.Date;
                            if (dicDate.ContainsKey(payDate))
                            {
                                if (dicDate[payDate].ContainsKey(payDT.OrgSid))
                                {
                                    dicDate[payDate][payDT.OrgSid].Add(payDT);
                                }
                                else
                                {
                                    List<TemppaymentDT> list = new List<TemppaymentDT>();
                                    list.Add(payDT);
                                    dicDate[payDate].Add(payDT.OrgSid, list);
                                }
                            }
                            else
                            {
                                List<TemppaymentDT> list = new List<TemppaymentDT>();
                                list.Add(payDT);
                                Dictionary<int, List<TemppaymentDT>> dicOrg = new Dictionary<int, List<TemppaymentDT>>();
                                dicOrg.Add(payDT.OrgSid, list);
                                dicDate.Add(payDate, dicOrg);
                            }
                        }
                    }
                }

                SqlTransaction tran = conn.BeginTransaction();

                try
                {
                    foreach (KeyValuePair<DateTime, Dictionary<int, List<TemppaymentDT>>> kvpDate in dicDate)
                    {
                        DateTime payDate = kvpDate.Key;
                        Dictionary<int, List<TemppaymentDT>> dicOrg = kvpDate.Value;
                        foreach (KeyValuePair<int, List<TemppaymentDT>> kvp in dicOrg)
                        {
                            using (SqlCommand comm = new SqlCommand())
                            {
                                comm.Transaction = tran;
                                comm.Connection = conn;

                                Guid invoiceID = Guid.NewGuid();
                                decimal PrincipalAmount = 0;
                                decimal InterestAmount = 0;
                                DateTime invoiceDateTime = payDate.AddDays(1);
                                //1.计算账单本金总和，同时更新Payment
                                string sqlPayment = string.Empty;
                                for (int j = 0; j < kvp.Value.Count; j++)
                                {
                                    PrincipalAmount += kvp.Value[j].PaymentAmount;

                                    sqlPayment += string.Format("update tEmpPayment set [InvoiceID]='{0}',[InvoiceDate]='{1}',[InvoiceStatus]={2} where Sid={3};",
                                        invoiceID, invoiceDateTime, (int)PaymentStatuses.DealDone, kvp.Value[j].Sid);
                                }


                                //2.计算账单利息总和
                                InterestAmount = TinvoicelistBN.CalcInterest(PrincipalAmount);

                                //3.生成Invoice
                                string sqlTempClosingBalance = string.Format("([dbo].[UF_GetCreditBalance]({0}))+ {1}", kvp.Key, PrincipalAmount);
                                string sqlTempCurrentBalance = string.Format("([dbo].[UF_GetFeeBalance]({0}))+ {1}", kvp.Key, (PrincipalAmount + InterestAmount));
                                //string sqlTempClosingBalance=string.Format("(SELECT [ClosingBalance]  FROM [tOrgInfo] WHERE [Sid]={0})+ {1}",kvp.Key,PrincipalAmount);
                                //string sqlTempCurrentBalance= string.Format("(SELECT [CurrentBalance]  FROM [tOrgInfo] WHERE [Sid]={0})+ {1}",kvp.Key,(PrincipalAmount + InterestAmount));

                                string sqlPreviousInvoiceID = string.Format("[dbo].[UF_GetLastInvoiceID]({0})", kvp.Key);
                                string sqlPreviousBalance = string.Format("[dbo].[UF_GetLastInvoiceAmount]({0})", kvp.Key);
                                string sqlPaidFromPreviousBalance = string.Format("[dbo].[UF_GetAllPaidFromLastInvoice]({0})", kvp.Key);

                                string sqlInvoice = string.Format("Insert into [tInvoiceList] ([OrgSid],[InvoiceDate],[Principal],[Interest],[IsValid],[Note],[Penalty],[LateInterestCharges],[Guid],[PaymentDate],[LastCreditBalance],[LastFeeBalance],[PreviousBalance],[PaidFromPreviousBalance],[PreviousInvoiceID]) VALUES ({0},'{1}',{2},{6},{4},'',0,0,'{3}','{5}',{7},{8},{9},{10},{11});",
                                    kvp.Key, invoiceDateTime, PrincipalAmount, invoiceID, (int)InvoiceStatuses.InitStatus, HiLand.Utility.Data.SafeDateTime.Min, InterestAmount, sqlTempClosingBalance, sqlTempCurrentBalance, sqlPreviousBalance,sqlPaidFromPreviousBalance,sqlPreviousInvoiceID);
                                
                                //4.同时将Invoice数据保存在资金流动表内
                                Guid flowingID = Guid.NewGuid();
                                int adminUserID = 1;//管理员暂时使用1
                                string sqlCaptionFollowing = string.Format("INSERT INTO [tCapitalFlowing] ([OrgID],[Direction],[Principal],[Interest],[Penalty],[LateInterestCharges],[Total],[Memo],[TransDate],[TransOperater],[Other1],[Other2],[Other3],[Other4],[ID],[LastCreditBalance],[LastFeeBalance]) VALUES ({0},{1},{2},{3},{4},{5},{6},'{7}','{8}',{9},{10},{11},'{12}','{13}','{14}',{15},{16});",
                                     kvp.Key, (int)CapitalFlowingDirections.Pay, 0 - PrincipalAmount, 0 - InterestAmount, 0, 0, 0 - (PrincipalAmount + InterestAmount), "", SafeDateTime.LocalNow, adminUserID, 0, 0, "", "", flowingID, sqlTempClosingBalance,sqlTempCurrentBalance);

                                string sqlCaptionFollowingRating = string.Format("INSERT INTO [tCapitalFlowingRelating] ([Direction] ,[CapitalFlowingID] ,[RalatingID] ,[Memo]) VALUES ({0} ,'{1}' ,'{2}' ,'{3}');",
                                    (int)CapitalFlowingDirections.Pay, flowingID, invoiceID, "invoice pay");


                                //5.更新用户的信用情况
                                string sqlCredit = string.Format("UPDATE [tOrgInfo]   SET [FeeBalance] = [FeeBalance]+{1},[CreditBalance] = [CreditBalance]+{2} WHERE Sid={0};", kvp.Key, PrincipalAmount + InterestAmount, PrincipalAmount);

                                comm.CommandText = sqlPayment + sqlInvoice + sqlCaptionFollowing + sqlCaptionFollowingRating + sqlCredit;
                                comm.ExecuteNonQuery();
                            }
                        }
                    }

                    tran.Commit();
                }
                catch (Exception ex)
                {
                    DataBaseLoger.SaveExcuteInfo("InvoiceTask", "Invoice Fail:" + ex.Message);
                    tran.Rollback();
                }
            }
        }
    }
}
